package edu.njfu.sas.dao.impl;


import edu.njfu.sas.dao.StudentDao;
import edu.njfu.sas.model.Student;
import edu.njfu.sas.util.DBHelper;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class StudentDaoImpl implements StudentDao {
    DBHelper db = new DBHelper();
    @Override
    public List<Student> getAllStudents() {
        String sql="select * from tbl_student;";
        List<Object> result = db.query(sql,null);
        List<Student> students=new ArrayList<>();
        for(Object o:result){
            Map map=(Map)o;
            students.add(
                    new Student(
                            (String)map.get("stuno"),
                            (String)map.get("stuname"),
                            (String)map.get("classes"),
                            (String)map.get("gender"),
                            (String)map.get("department"),
                            (String)map.get("tel"),
                            (String)map.get("dormno"),
                            (String)map.get("photopath")
                    )
            );
        }
        return students;
    }

    @Override
    public List<Student> getStudentsByStr(String str) {
        String sql="select * from tbl_student where stuname like ? or stuno like ? or classes like ? or department like ? or dormno like ? or tel like ? or  gender like ?;";
        List<Object> params=new ArrayList<>();
        params.add("%"+str+"%");
        params.add("%"+str+"%");
        params.add("%"+str+"%");
        params.add("%"+str+"%");
        params.add("%"+str+"%");
        params.add("%"+str+"%");
        params.add("%"+str+"%");
        List<Object> result=db.query(sql, params);
        List<Student> students=new ArrayList<>();
        for(Object o:result){
            Map map=(Map)o;
            students.add(
                    new Student(
                            (String)map.get("stuno"),
                            (String)map.get("stuname"),
                            (String)map.get("classes"),
                            (String)map.get("gender"),
                            (String)map.get("department"),
                            (String)map.get("tel"),
                            (String)map.get("dormno"),
                            (String)map.get("photopath")
                    )
            );
        }
        return students;
    }

    @Override
    public boolean saveStudent(Student s) {
        String sql="insert into tbl_student(stuno,stuname,classes,gender,department,tel,dormno) values" +
                "(?,?,?,?,?,?,?);";
        List<Object> params=new ArrayList<>();
        params.add(s.getStuNo());
        params.add(s.getStuName());
        params.add(s.getClasses());
        params.add(s.getGender());
        params.add(s.getDepartment());
        params.add(s.getTel());
        params.add(s.getDormNo());
        int result = db.update(sql,params);
        //System.out.println(s.toString());
        return result>0;
    }

    @Override
    public long totalRecords() {
        String sql="select count(*) as count from tbl_student;";
        List<Object> query =db.query(sql,null);
        return (long)((Map)query.get(0)).get("count");
    }

    @Override
    public boolean deleteStudent(String stuno) throws SQLException {
        String sql="delete from tbl_student where stuno='"+stuno+"';";
        int result = db.update(sql);
        return result>0;
    }

    @Override
    public boolean updateStudent(Student s) {
        String sql="update tbl_student set stuname=?, classes=?, gender=?, department=?, tel=?, dormno=? where stuno=?;";
        List<Object> params=new ArrayList<>();
        params.add(s.getStuName());
        params.add(s.getClasses());
        params.add(s.getGender());
        params.add(s.getDepartment());
        params.add(s.getTel());
        params.add(s.getDormNo());
        params.add(s.getStuNo());
        int result = db.update(sql,params);
        return result>0;
    }

    @Override
    public List<Student> getStudentsByPage(int currentPage, int pageSize) {
        String sql="select * from tbl_student where stuno limit ?,?;";
        int start=(currentPage-1)*pageSize;
        List<Object> params=new ArrayList<>();
        params.add(start);
        params.add(pageSize);
        List<Object> query=db.query(sql,params);
        List<Student> students=new ArrayList<>();
        for(Object o:query){
            Map map=(Map)o;
            students.add(
                    new Student(
                            (String)map.get("stuno"),
                            (String)map.get("stuname"),
                            (String)map.get("classes"),
                            (String)map.get("gender"),
                            (String)map.get("department"),
                            (String)map.get("tel"),
                            (String)map.get("dormno"),
                            (String)map.get("photopath")
                    )
            );
        }
        return students;
    }

    @Override
    public Student getStudentByNo(String stuno) {
        String sql="select * from tbl_student where stuno='"+stuno+"';";
        List<Object> query=db.query(sql,null);
        Map map=(Map)query.get(0);
        return  new Student(
                (String)map.get("stuno"),
                (String)map.get("stuname"),
                (String)map.get("classes"),
                (String)map.get("gender"),
                (String)map.get("department"),
                (String)map.get("tel"),
                (String)map.get("dormno"),
                (String)map.get("photopath")
        );
    }

}
